import MySQLdb,re
import sys
import cPickle

conn=MySQLdb.connect(host='198.133.224.185',user='root',passwd='root',db="scan")
TOP=5

def get_tables():
	sql="show tables"
	cur=conn.cursor()
	cur.execute(sql)
	tres=cur.fetchall()
	res=[]
	for t in tres:
		if t[0].startswith("scanner_"):
			res.append(t[0])
	cur.close()
	return res[0:12]
def export():
	sql="select ip,code,time from ip_sig_st into outfile '/tmp/churn.txt' fields terminated by ','"
	pass

def dbs(fin="ip_reg_prex.csv"):
	ip_reg={}
	ip_prex={}
	for i in open(fin):
		i=i.strip("\n").split(",")
		ip_reg[i[0]]=i[1]
		ip_prex[i[0]]=i[2]

	return ip_reg,ip_prex


ip_reg,ip_prex=dbs()

def process_file(fin="ip_port_time.csv"):
	all_date={}
	for i in open(fin):
		i=i.strip("\n").split(",")[2]
		all_date[i]=1
	all_date=all_date.keys()
	all_date.sort()
	
	st_date=all_date[0]
	ed_date=all_date[len(all_date)-1]
	print st_date,ed_date
	"""
	1st cluster:
	if shut down
	"""
	ip_stat={}
	for i in open(fin):
		i=i.strip("\n").split(",")
		if i[0] in ip_stat:
			ip_stat[i[0]].append(i[2])
		else:
			ip_stat[i[0]]=[]
			ip_stat[i[0]].append(i[2])
	targets={}
	for i in ip_stat:
		ip_stat[i].sort()
		if ip_stat[i]==all_date:
			continue
		else:
			targets[i]=ip_stat[i]
	#get all churn instances
	stat_top_reg_prex(ip_stat)
	print len(targets)

	"""
	2nd cluster: 
	4 basic patterns,a=active,i=inactive:
	1.a_i_a
	2.i_a
	3.a_i
	4.i_a_i

	"""
	churn_clus={}
	churn_clus["aia"]=[]
	churn_clus["ia"]=[]
	churn_clus["ai"]=[]
	churn_clus["iai"]=[]
	for i in targets:
		_dates=targets[i]
		if st_date in _dates:
			if ed_date in _dates:
				churn_clus["aia"].append(i)
			else:
				churn_clus["ai"].append(i)
		else:
			if ed_date in _dates:
				churn_clus["ia"].append(i)
			else:
				churn_clus["iai"].append(i)			
	
	for c in churn_clus:
		print c,len(churn_clus[c])
		stat_top_reg_prex(churn_clus[c])

	f=open("clus.db","w")
	cPickle.dump(churn_clus,f)
	f.close()
	f=open("clus_stat.db","w")
	cPickle.dump(targets,f)
	f.close()

def stat_top_reg_prex(clus):
	reg_res={}
	prex_res={}
	total=len(clus)
	for k in clus:
		_reg=ip_reg[k]
		_prex=ip_prex[k]
		if _reg in reg_res:
			reg_res[_reg]+=1
		else:
			reg_res[_reg]=1

		if _prex in prex_res:
			prex_res[_prex]+=1
		else:
			prex_res[_prex]=1
	reg_res=sorted(reg_res.iteritems(), key=lambda d:d[1], reverse = True)[0:TOP]
	prex_res=sorted(prex_res.iteritems(), key=lambda d:d[1], reverse = True)[0:TOP]
	print reg_res
	print prex_res
	pass

def prepare_i_err_a(ipf="clus.db",stf="clus_stat.db",errtype="ia"):
	tables=get_tables()
	#get the instances that follow pattern close-active
	clus=cPickle.load(open(ipf))[errtype]
	print len(clus)
	clus_st=cPickle.load(open(stf))
	print len(clus_st)

	final_clus={}
	for i in clus:
		final_clus[i]=clus_st[i]
	
	print len(final_clus)

	"""
	#get the error instance and time
	for t in tables:
		print t
		sql="select ip,content,time from %s where code='0'"%(t)
		cur=conn.cursor()
		cur.execute(sql)
		res=cur.fetchall()
		f=open("%s_err.db"%(t),"w")
		cPickle.dump(res,f)
		f.close()
		cur.close()
	"""
	errs={}
	i=0
	dates=[]
	for t in tables:
		dn="%s_err.db"%(t)
		t=cPickle.load(open(dn))
		for _t in t:
			errs[_t[0]]={}
			errs[_t[0]][_t[2]]=2
			dates.append(_t[2])
	dates=list(set(dates))
	dates.sort()
	res={}
	for ip in final_clus:
		res[ip]=[]
		for d in dates:
			if d in final_clus[ip]:
				#res[ip][d]=1
				res[ip].append(1)
				continue
			try:
				#res[ip][d]=errs[ip][d]
				res[ip].append(errs[ip][d])
			except:
				res[ip].append(0)
				#res[ip][d]=0

	#print res
	f=open("tmp.db","w")
	cPickle.dump(res,f)
	f.close()

def generate_ip_status(fin="ip_port_time.csv"):
	all_date={}
	for i in open(fin):
		i=i.strip("\n").split(",")[2]
		all_date[i]=1
	all_date=all_date.keys()
	all_date.sort()
	errs={}
	for t in tables:
		dn="%s_err.db"%(t)
		t=cPickle.load(open(dn))
		for _t in t:
			errs[_t[0]]={}
			errs[_t[0]][_t[2]]=2

	ip_stat={}
	for i in open(fin):
		i=i.strip("\n").split(",")
		if i[0] in ip_stat:
			ip_stat[i[0]].append(i[2])
		else:
			ip_stat[i[0]]=[]
			ip_stat[i[0]].append(i[2])
	res={}
	for ip in ip_stat:
		ip_stat[ip].sort()



def search_i_err_a(fin="tmp.db"):
	db=cPickle.load(open(fin))
	print len(db)
	res={}
	for i in db:
		s="".join([str(ti) for ti in db[i]])
		if s in res:
			res[s].append(i)
		else:
			res[s]=[]
			res[s].append(i)
	print len(res)
	for i in res:
		if "2" in i:
			print i,res[i]


if __name__ == '__main__':
	tables=get_tables()
	#print tables
	#search_i_err_a()
	#process_file()
	for t in tables:
		print t
		sql="select ip,time from %s where port='22'"%(t)
		cur=conn.cursor()
		cur.execute(sql)
		res=cur.fetchall()
		f=open("%s_22.db"%(t),"w")
		cPickle.dump(res,f)
		f.close()
		cur.close()
